﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using Aden.Model.Stock;
using Aden.Model.Common;
using Aden.Util.Database;
using Aden.Util.Common;

namespace Aden.DAL.StockData
{
    public enum TransTypes { Stockin, Stockout }

    public class StockTransfer
    {
        protected string BusinessType = "Transfer";
        protected string CostCenterCode = "";
        protected string AppGuid = "";
        protected string OrderDate = DateTime.Now.ToString("yyyy-MM-dd");
        protected string CreateUser = "";

        protected StockTrans stocktrans = null;

        public StockTransfer(string appGuid, string costCenterCode, string orderDate, string createUser)
            : this(appGuid,costCenterCode,"Transfer", orderDate, createUser) { }

        public StockTransfer(string appGuid, string costCenterCode, string businessType, 
            string orderDate, string createUser)
        {
            this.AppGuid = appGuid;
            this.CostCenterCode = costCenterCode;
            this.BusinessType = businessType;
            this.OrderDate = orderDate;
            this.CreateUser = createUser;

            if(!string.IsNullOrWhiteSpace(CostCenterCode))
                stocktrans = new StockTrans(CostCenterCode);
        }

        public virtual List<Dictionary<string, object>> GetItems(StockParam param = null) //Dictionary<string, string> param = null
        {
            try
            {
                return stocktrans.GetStockCost(OrderDate).Select(q =>
                {
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    dic.Add("itemCode", q.itemCode);
                    dic.Add("itemName", q.itemName);
                    dic.Add("uom", q.uom);
                    dic.Add("stockQty", q.stockQty);
                  
                    dic.Add("costs", q.lines.Select(p =>
                    {
                        Dictionary<string, decimal> d = new Dictionary<string, decimal>();
                        d.Add("itemCost", p.itemCost);
                        d.Add("stockQty", p.stockQty);

                        return d;
                    }).ToList());

                    return dic;
                }).ToList();
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public virtual List<Dictionary<string, object>> GetDates()
        {
            try
            {
                List<Dictionary<string, object>> dic = new List<Dictionary<string, object>>();
                // 最大的出库日期
                var ds = stocktrans.GetLastOutDate();
                // DateTime endtime = new AppProcess.AppProcessFactory().GetEndTime("40820A3F-3A22-4FC8-9F7B-85255015AA50", CostCenterCode, null);
                // 最小的未入库日期 - 有待修改
                // var de = SqlServerHelper.GetDataScalar(SqlServerHelper.salesorderConn(),string.Format(stocktrans.GetFirstNotInStockDate(), endtime.TimeOfDay));

                var de = SqlServerHelper.GetDataScalar(SqlServerHelper.salesorderConn(), string.Format(stocktrans.GetFirstNotInStockDate(), " 16:00"));
                
                if (ds == null)
                    return null;
                DateTime date1 = DateTime.Compare((DateTime)ds, DateTime.Parse("2020-11-30"))>0? (DateTime)ds: DateTime.Parse("2020-11-30"); // 从2920-12-01开始消耗
                DateTime date2 = (de == null || string.IsNullOrWhiteSpace(de.ToString()) || DateTime.Compare((DateTime)de, DateTime.Now) > 0) ? DateTime.Now: ((DateTime)de).AddDays(-1);
                while ((date1= date1.AddDays(1)) <= date2)
                {
                    dic.Add(new Dictionary<string, object>() { { "date", date1.ToString("yyyy-MM-dd") } });
                } 
                return dic;
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public virtual Dictionary<string, object> ShowComponents()
        {
           
            Dictionary<string, object> dic = new Dictionary<string, object>();
           
            dic["Item"] = new Dictionary<string, object>() { { "change", false } };
            dic["ItemDesc"] = new Dictionary<string, object>() { { "visible", false } };
            dic["Price"] = new Dictionary<string, object>() { { "visible", false }, { "change", false } };
            dic["QrderQty"] = new Dictionary<string, object>() { { "visible", false } };
            dic["StockQty"] = new Dictionary<string, object>() { { "visible", false } };
            dic["ToCostCenter"] = new Dictionary<string, object>() { { "visible", false } };
            dic["BusinessType"] = new Dictionary<string, object>() { { "visible", false }, { "change", false } };
            
            return dic;
        }


        /*
         * 返回值：
         * 1 null： 存盘成功
         * 2 错误：key： Error，data         * 
         */
        public virtual Dictionary<string, object> save(StockParam param) //Dictionary<string, string> param,List<TransData> data
        {
            try
            {
                save(param.data, null);
                return null;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void save(List<TransData> data,  TransTypes? transType, List<SQL> checkSqls = null)
        {
            try
            {
                IEnumerable<TransData> tmp = data.Where(q => q.qty > 0);
                if (tmp == null || !tmp.Any()) throw new Exception("No data to save");

                string transGuid = Guid.NewGuid().ToString("N");

                List<SQL> sqls = new List<SQL>();
                if (checkSqls != null && checkSqls.Any()) sqls.AddRange(checkSqls);

                if (transType == null || transType == TransTypes.Stockout)
                   sqls.AddRange(GetOutSQL(transGuid,tmp));


                if(transType == null || transType == TransTypes.Stockin)
                    sqls.AddRange(GetInSQL(transGuid,tmp));

                SqlServerHelper.Execute(SqlServerHelper.salesorderConn(), sqls);
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        private string[] AddFields(TransData data, TransTypes transType, string[] fields)
        {
            string[] sqls = { "", "" };
            if (data == null || fields == null || !fields.Any()) return sqls;
            
            Type type = typeof(TransData);
            Func<string, string> getVal = (f) =>
             {
                 PropertyInfo p = type.GetProperty(f.ToStringTrim(), BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                 if (p == null) return null;

                 string val = p.GetValue(data).ToStringTrim();
                 if (string.IsNullOrWhiteSpace(val)) return null ;

                 try
                 {
                     if (p.PropertyType.Name != "Decimal" && p.PropertyType.GenericTypeArguments[0].Name != "Decimal")
                        throw new Exception("");

                    if (decimal.Parse(val) == 0) return null; ;
                    val = string.Format("'{0}'", decimal.Parse(val).ToString("#0.######"));                         
                 }
                 catch
                 {
                     val = string.Format("N'{0}'", val);
                 }
                 return val;
             };

            if (transType == TransTypes.Stockin)
            {
                string sf = "customercode,itemprice,stockqty";
                if (fields.Any(q => q.ToLower().Trim() == "itemprice") && fields.Any(q => q.ToLower().Trim() == "itemcost"))
                {  //price => cost
                    sf += ",itemcost";
                    string sprice = getVal("itemprice");
                    string scost = getVal("itempcost");
                    if(!string.IsNullOrWhiteSpace(sprice) && string.IsNullOrWhiteSpace(scost))
                    {
                        sqls[0] += ",ItemCost";
                        sqls[1] += $",{sprice}";
                    }
                }

                fields = fields.Where(q => !sf.Contains(q.ToLower())).ToArray();
            }
            else if (transType == TransTypes.Stockout)
            {
                fields = fields.Where(q => q.ToLower() != "suppliercode").ToArray();
                if (data.isServiceItem) //服务产品出库
                    fields = fields.Where(q => !"itemcost,stockqty".Contains(q.ToLower())).ToArray();
            }

            foreach (string s in fields)
            {
                string val = getVal(s);
                if (string.IsNullOrWhiteSpace(val)) continue;

                sqls[0] += "," + s;
                // Angel修改：N避免乱码
                sqls[1] += "," + val;
            }
            return sqls;
        }

        private List<SQL> GetSaveSQL(string transGuid, TransTypes transType, IEnumerable<TransData> data,
            List<string> sqlChecks)
        {

            if (data == null || !data.Any()) return null;

            string fields = "ItemName,uom, qty,StockQty,ItemPrice,ItemCost,SupplierCode,OrderLineGuid,OrderQty,Remark,isServiceItem ";

            string sql = "";
            if(sqlChecks != null && sqlChecks.Any())
                sql = " where " + string.Join(" and ", sqlChecks.Select(q => $"not exists({q})"));

            sql = " ;insert into tblTransData(TransGuid,BusinessType,TRANSDATE,CreateUser,CostCenterCode,ItemCode {2}) " +
                $"select '{transGuid}','{BusinessType}','{OrderDate}','{CreateUser}', " +
                "'{0}','{1}' {3} " + sql;

            List<string> tmpsqls = data.Select(q =>
            {
                string[] sqlF = AddFields(q, transType, fields.Split(','));

                //转仓入的,ToCostCenter
                string costCenterCode = CostCenterCode;
                if (transType == TransTypes.Stockin && !string.IsNullOrWhiteSpace(q.toCostCenterCode))
                    costCenterCode = q.toCostCenterCode.ToStringTrim();
              
                return string.Format(sql, costCenterCode, q.itemCode, sqlF[0], sqlF[1]);
               
            }).ToList();

            return new List<SQL> { 
                new SQL
                {
                    sqls = string.Join(";",tmpsqls),
                    ExeRecordCount = tmpsqls.Count,
                    error = "Error to save " + transType.ToStringTrim()
                }
            };
        }


        private List<SQL> GetInSQL(string transGuid, IEnumerable<TransData> dIN)
        {
            dIN = dIN.Where(q => q.qty > 0);
            if (!dIN.Any()) return null;

            //没有明天及以后的出库
            string sqlcheck = "select top 1 1 from tblTransData (nolock) " +
                $"where CostCenterCode='{CostCenterCode}' and TRANSDATE>'{OrderDate}' and TransGuid<>'{transGuid}'" +
                " and ItemCode='{1}' and isnull(Qty,0)<0 and isnull(IsServiceItem,0)=0";
            return GetSaveSQL(transGuid, TransTypes.Stockin, dIN, new List<string> { sqlcheck });
        }

        private List<SQL> GetOutSQL(string transGuid, IEnumerable<TransData> dOUT)
        {
            dOUT = dOUT.Where(q => q.qty != 0);
            //出库记录
            if (!dOUT.Any()) return null;
            if (dOUT.Any(q => q.qty > 0) && dOUT.Any(q => q.qty < 0))
                throw new Exception("Error in stockout data");

            string startTime = DateTime.Now.AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss"); //检查tblTansData中是否有新记录

            #region Stock item - FIFO
            var tStock = dOUT.Where(q => !q.isServiceItem);
            List<string> sqlchecks = new List<string>();
            if (tStock.Any())
            {
                //refresh stock from database
                List<TransData> items = stocktrans.GetStockCost(OrderDate);
                var tjoin = tStock.GroupJoin(items, a => a.itemCode.ToStringTrim(), b => b.itemCode.ToStringTrim(),
                    (a, b) => new { a, b });
                if (tjoin.Any(q => q.b == null || !q.b.Any() || q.a.qty > q.b.Max(p => p.stockQty)))    //没有库存或库存不足
                    throw new Exception("Shortage of some stock");

                tStock = tjoin.SelectMany(q =>
                    {
                        List<TransData> list = new List<TransData>();

                        decimal qty = Math.Abs(q.a.qty ?? 0);
                        q.b.FirstOrDefault().lines.ForEach(p =>
                        {
                            if (qty <= 0) return;

                            TransData tmp = (TransData)q.a.Clone();
                            tmp.itemCost = p.itemCost;
                            tmp.qty = -1 * Math.Min(qty, p.stockQty);

                            qty -= p.stockQty;

                            list.Add(tmp);
                        });

                        if (qty > 0) throw new Exception("Shortage of the stock of " + q.a.itemCode);
                        return list;
                    });

                //startTime开始没有出库，或者以前的入库
                //sqlchecks.Add("select top 1 1 from tblTransData (nolock) where ItemCode='{1}' and IsServiceItem=0 and " +
                //    $"CostCenterCode='{CostCenterCode}' and CreateTime>='{startTime}' and TransGuid<>'{transGuid}'" +
                //    $" and  (isnull(Qty,0)<0 or (isnull(Qty,0)>0 and orderDate<'{OrderDate}'))");

                sqlchecks.Add("select top 1 1 from tblTransData (nolock) where ItemCode='{1}' and isnull(IsServiceItem,0)=0 and " +
                    $"CostCenterCode='{CostCenterCode}' and TransGuid<>'{transGuid}'" +
                    $" and ((isnull(Qty,0)<0 and TRANSDATE>'{OrderDate}') or " +  //OrderDate之后的出库
                    $"(CreateTime>='{startTime}' and (isnull(Qty,0)<0 or (isnull(Qty,0)>0 and TRANSDATE<'{OrderDate}'))))");

                //检查tblOrderLines之前的ItemCode已收货
                string sql = (new Purchase.PurchaseOrderFactory()).GetOpenPOSQL(
                    CostCenterCode, string.Format("<='{0}'", OrderDate), " and isnull(A.RemainQty, 0) <> 0 and A.RemainQty = A.Qty {1}");

                sql = string.Format(sql, "top 1 1", "and A.ItemCode='{1}'");

                //没有以前的order
                sqlchecks.Add(sql);
            }
           
            if (dOUT.Any(q => !string.IsNullOrWhiteSpace(q.orderLineGUID)))
            {
                string orderLineGuids = string.Join("','", dOUT.Select(q => q.orderLineGUID));
                sqlchecks.Add("select top 1 1 from tblTransData where " + 
                        $"TransGuid<>'{transGuid}' and costCenterCode='{CostCenterCode}' and  TRANSDATE='{OrderDate}' and OrderLineGuid  in ('{orderLineGuids}')");
            }
            #endregion
            List<TransData> tt = new List<TransData>();
            tt.AddRange(dOUT.Where(q => q.isServiceItem).Select(q => { q.qty = -1 * Math.Abs(q.qty ?? 0); return q; }));
            tt.AddRange(tStock);

            return GetSaveSQL(transGuid,TransTypes.Stockout, tt, sqlchecks);
        }

        /*
        private StringBuilder GetSaveSQL_bak(List<TransData> data, string date, string user)
        {
            string sqlin = " ;insert into tblTransData(TransGuid,TransDate,CostCenterCode,ItemCode,ItemName,ItemCost,Qty,CreateUser {7}) " +
                "values('" + Guid.NewGuid().ToString() + "','{0}','{1}','{2}','{3}',{4},{5},'{6}' {8})";
            string sqlout = " ;insert into tblTransData(TransGuid,TransDate,CostCenterCode,ItemCode,ItemName,ItemCost, Qty,CreateUser {7}) " +
                "select '" + Guid.NewGuid().ToString() + "','{0}','{1}','{2}','{3}',{4}," +
                "-(case when {5} >= (a1.runningqty + isnull(a2.outqty, 0)) then " +                 //出库量 > 本次入库后的总入库存
                "(case when(a1.runningqty + isnull(a2.outqty, 0)) >= a1.qty then a1.qty " +             //取本次剩余入库数量
                "else a1.runningqty + isnull(a2.outqty, 0) end) " +                                         //本入库抵扣过以前的出库
                "else {5} - (case when(a1.runningqty + isnull(a2.outqty, 0)) > a1.qty then " +      //出库量 < 本次入库后的总入库存
                "a1.runningqty + isnull(a2.outqty, 0) - a1.qty else 0 end) end) " +                     //出库量 - 本入库前的库存
                "'{6}', {8} "
                + GetStockSQL(null, "{2}") +
                " and (a1.totalqty + isnull(a2.outqty, 0)) >= {5} and " +                           //出库量 <= 总入库 
                "(case when(a1.runningqty + isnull(a2.outqty, 0)) > a1.qty then " +
                "a1.runningqty + isnull(a2.outqty, 0) - a1.qty else 0 end) < {5} ";                   //出库量 > 本入库前的库存 

            StringBuilder sqls = new StringBuilder();
            if (data == null || !data.Any()) return sqls;

            //添加需要插入的字段
            Type type = typeof(TransData);
            string sqlf = "";
            string sqlv = "";
            Action<TransData> action = (line) =>
            {
                string[] fields = { "StockQty", "ItemPrice", "CustomerCode", "SupplierCode", "OrderLineGuid", "OrderQty" };
                if (line == null || fields == null || !fields.Any()) return;
                foreach (string s in fields)
                {
                    PropertyInfo p = type.GetProperty(s, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                    if (p == null) continue;

                    string val = p.GetValue(line).ToStringTrim();
                    if (string.IsNullOrWhiteSpace(val)) continue;
                    if (val == "0" && (p.PropertyType.Name == "Decimal" ||
                      p.PropertyType.GenericTypeArguments[0].Name == "Decimal"))
                        continue;

                    sqlf += "," + p.Name;
                    sqlv += string.Format(",'{0}'", val);
                }
            };

            string[] transTypes = { "Transfer", "Stockadj" };
            data.Where(q => (q.qty ?? 0) != 0).ForEach(q => {
                sqlf = "";
                sqlv = "";

                action(q);
               // if (transTypes.Contains(TransType) && q.qty < 0 || TransType == "Stockout")
                {
                    sqls.AppendFormat(sqlout, date, q.costCenterCode, q.itemCode, q.itemName, "a1.itemcost",
                        Math.Abs(q.qty ?? 0), user, sqlf, sqlv);
                    return;
                }

                sqls.AppendFormat(sqlin, date, q.costCenterCode, q.itemCode, q.itemName, q.itemCost,
                    q.qty, user, sqlf, sqlv);
            });

            GetOutSQL(Guid.NewGuid().ToString("N"), data, "2020-6-30", "steve.weng");

            return sqls;
        }
        */
    }
}
